/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Taxi.db;

import Taxi.model.Taxi;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

/**
 *
 * @author BIGBOSS
 */
public class TaxiDAO {

    public int save(Taxi taxi) throws SQLException {
        Statement statement;
        ResultSet rs;
        statement = (Statement) Database.getConnection().createStatement();
        return statement.executeUpdate("INSERT INTO vehicle VALUES( "
                + taxi.getUnitNumber() + " , " + "'"
                + taxi.getPlateNumber() + "' , " + "'"
                + taxi.getStartDate() + "' , " + "'"
                + taxi.getEndDate() + "' , "
                + +taxi.getStartOdometer() + " , "
                + +taxi.getEndOdometer() + " , " + "'"
                + taxi.getOrigColor() + "' , " + "'"
                + taxi.getNewOld() + "' , " + "'"
                + taxi.getEngineNumber() + "' , " + "'"
                + taxi.getChasisNumber() + "' , " + "'"
                + taxi.getGreenPlate() + "' , " + "'"
                + taxi.getYellowPlate()
                + "')");
    }

    public int update(Taxi taxi) throws SQLException {
        System.out.println("UPDATE");
        Statement statement;
        ResultSet rs;
        statement = (Statement) Database.getConnection().createStatement();
        return statement.executeUpdate("UPDATE vehicle SET unitnumber="
                + taxi.getUnitNumber() + ", plate_number='"
                + taxi.getPlateNumber() + "', start_date='"
                + taxi.getStartDate() + "', end_date='"
                + taxi.getEndDate() + "',start_odometer="
                + taxi.getStartOdometer() + ",end_odometer="
                + taxi.getEndOdometer() + ",origcolor='"
                + taxi.getOrigColor() + "',bneworsecond='"
                + taxi.getNewOld() + "',enginenumber='"
                + taxi.getEngineNumber() + "',chasisnumber='"
                + taxi.getChasisNumber() + "',greenplate='"
                + taxi.getGreenPlate() + "',yellowplate='"
                + taxi.getYellowPlate() + "' WHERE plate_number='"
                + taxi.getPlateNumber() + "'");
    }

// sample UPDATE employee SET emp_num =2 WHERE emp_num =1 AND first_name ='Chan' AND last_name='Howard' AND address='129378 askhdao 1' AND birthday='2000-10-20' AND license='qiweju123734';
    public Taxi getTaxiByPlateNumber(String unitnumber) throws SQLException {
        Statement statement;
        ResultSet rs;
        statement = (Statement) Database.getConnection().createStatement();
        rs = statement.executeQuery("SELECT * FROM VEHICLE WHERE unitnumber='"
                + unitnumber + "'");
        Taxi taxi = new Taxi();
        rs.next();
        taxi.setUnitNumber(rs.getInt("unitnumber"));
        taxi.setPlateNumber(rs.getString("plate_number"));
        taxi.setStartDate(rs.getString("start_date"));
        taxi.setEndDate(rs.getString("end_date"));
        taxi.setStartOdometer(rs.getInt("start_odometer"));
        taxi.setEndOdometer(rs.getInt("end_odometer"));
        taxi.setOrigColor(rs.getString("origcolor"));
        taxi.setNewOld(rs.getString("bneworsecond"));
        taxi.setEngineNumber(rs.getString("enginenumber"));
        taxi.setChasisNumber(rs.getString("chasisnumber"));
        taxi.setGreenPlate(rs.getString("greenplate"));
        taxi.setYellowPlate(rs.getString("yellowplate"));
        return taxi;
    }

    public void deleteByPlateNumber(String platenumber) {
        try {
            Statement statement;
            ResultSet rs;
            statement = (Statement) Database.getConnection().createStatement();
            int i = statement.executeUpdate("DELETE FROM VEHICLE WHERE PLATE_NUMBER = '"
                    + platenumber + "'");
        } catch (SQLException insertException) {
            insertException.printStackTrace();
        }
    }

    public List<Taxi> getAllTaxi() throws SQLException {
        Statement statement;
        ResultSet rs;
        statement = (Statement) Database.getConnection().createStatement();
        rs = statement.executeQuery("SELECT * FROM VEHICLE");
        List<Taxi> taxis = new LinkedList<Taxi>();
        while (rs.next()) {
            Taxi taxi = new Taxi();
            taxi.setUnitNumber(rs.getInt("unitnumber"));
            taxi.setPlateNumber(rs.getString("plate_number"));
            taxi.setStartDate(rs.getString("start_date"));
            taxi.setEndDate(rs.getString("end_date"));
            taxi.setStartOdometer(rs.getInt("start_odometer"));
            taxi.setEndOdometer(rs.getInt("end_odometer"));
            taxi.setOrigColor(rs.getString("origcolor"));
            taxi.setNewOld(rs.getString("bneworsecond"));
            taxi.setEngineNumber(rs.getString("enginenumber"));
            taxi.setChasisNumber(rs.getString("chasisnumber"));
            taxi.setGreenPlate(rs.getString("greenplate"));
            taxi.setYellowPlate(rs.getString("yellowplate"));
            taxis.add(taxi);
        }
        return taxis;
    }

    public List<Integer> getAllTaxiUnitNumber() throws SQLException {
        Statement statement;
        ResultSet rs;
        statement = (Statement) Database.getConnection().createStatement();
        rs = statement.executeQuery("SELECT unitnumber FROM VEHICLE");
        List<Integer> unitNums = new LinkedList<Integer>();
        while (rs.next()) {
            unitNums.add(rs.getInt("unitnumber"));
        }
        return unitNums;
    }
}
